Skip to main content

06-01 introduction-to-charting

introduction to charting​

Charting is the second most important aspect of automatic Excel behind manipulating Ranges. There is a bias when saying that because a lot of what I do after engineering calculations is chart the results. In particular, Excel can be used to great effect to chart time series of data. The other reason charts are so amenable to VBA is that very often you are applying the same actions to the charts. In that sense, the VBA related to charts is doing a lot of changing settings and formats so that the charts look the way you want. This ahs the immediate effect of making your charts look less like "they came from Excel" which is a common knock in some circles.

When working with Charts, there is a Range of difficulties depending on what you are trying to do. In some cases, working with an existing chart is much easier than creating a new one. In other instances, it can be much simpler to create a new chart, starting from a default, rather than change all the settings back. One other major difference between Charts and Ranges is that working with charts is much more about knowing the object model than knowing how to program. The vast majority of your code related to charts is simply iterating through objects to find the one property that you want to change. This makes it easier to write chart VBA once you have the basics of For Each loops down. It also means that you need to spend some time getting comfortable with the object model.

There is one oddity related to Charts that is worth mentioning now. Charts can either be embedded as an object on a Worksheet, or they can be their own Sheets. I personally never use the latter case, but it is common enough that it needs to be on your mind when working with Charting code.

(I don't use the Chart as a Sheet model because I find that it is not necessary in terms of displaying data. In particular, you are at the mercy of your window size and cannot easily change the dimensions. Also, it complicates the VBA side of things to work in both formats all the time, so I just decided to always put my Charts on Sheets. Your mileage may vary so I'll touch on both approaches in the code samples.)

a quick overview of the object model​

  • ChartObjects -> ChartObject - this derives from Shape and exists when the Chart is on a Worksheet
    • Chart
      • SeriesCollection -> Series
      • Axes -> Axis
      • ChartArea
      • PlotArea
  • ActiveChart -> Chart - this works whether you have a Worksheet or Chart on a sheet
  • Selection -> Variant - this one can be useful but is often not of the type that you want.

obtaining a reference to a Chart​

When working with Charts, the first task is typically to get a reference to an existing chart -- unless you are creating a new chart. To obtain a reference to a chart, there are a handful of ways of doing it depending on what your spreadsheet contains and how it's structured.

THe main ways to do it are:

  • Use the ActiveChart object
  • Use the Selection object -- this is highly depending on what is selected
  • Use the ChartObjects object
    • If you know which chart you want, you can supply an index; this works great if there is only a single chart - ChartObjects(1)
    • If you want to do something to all charts, you can iterate this object
    • If you have named the chart (more on that later) you can supply the name as the index - ChartObjects("SomeChart")
  • The Workbook.Sheets object if your charts are contained in their own sheets
    • Same as above, you can access via a numeric index, name, or iterate through all of them

ActiveChart​

ActiveChart is similar to the other Active objects in that it does about what you expect. The one difference is that the Chart actually has to be selected or have focus in order to be considered "active". This is similar but also different to something like ActiveWorkbook where having the workbook open makes it active.

Note that ActiveChart will work for a Chart that is contained on a Worksheet or also for one that is its own Sheet. If the latter case, then ActiveSheet and ActiveChart will refer to the same object. Side note: this technicality is why you will not get proper Intellisense when using ActiveSheet -- that Sheet could technically be a Chart.

The nice thing about ActiveChart is that it gives you the Chart object which then gives you immediate access to the Chart related details you are like to want to change. The downside is that unless you have a single Chart that is already selected, ActiveChart has limited application when using VBA. Again, the goal is to avoid selecting objects in order to access them via VBA so ActiveChart is not ideal.

Selection​

The Selection object is probably the greatest catch all for an object. It literally holds anything, and this means that using the object requires knowing what is selected, or checking vigorously before using the object. Technically, you also let your code error out if the wrong object is selected, and this works well at times. This works well because you are unlikely to be using Selection in a complicated workflow because, again, you should not be selecting objects to access them. This means that Selection is really limited to one-off and helper code where you can more tightly dictate that this code only works if you select a Chart. You should still add some error handling, but sometimes that step is skipped.

Since the Selection can hold anything, it's important to know what could be Selected. Related to charts, the following can all live in the Selection:

  • ChartObjects
  • Chart
  • ChartArea
  • PlotArea
  • Legend
  • ChartTitle
  • Series

If you are writing VBA to work on Charts, you can technically require the user to select the correct part of the chart and always use Selection. You will quickly grow tired of having to remember which part of the Chart to select in order to make the code work. To avoid this scenario, it is helpful to remember the object model and know how to work your way around a Chart.

My approach has always been to convert the Selection to a Collection of ChartObjects. I can then always iterate that resulting Collection to process the Charts. If only a single Chart was selected, the code works all the same. The downside to this approach is that a Chart as a Sheet cannot live inside a ChartObject. This is a large part of why I always put Charts on a Worksheet.

Below is the helper function I use in order to convert a possibly Chart containing selection into a Collection of ChartObjects. It works for all objects except for the Axis related ones.

TODO: consider improving this code if it is included as a de facto reference

Public Function Chart_GetObjectsFromObject(ByVal inputObject As Object) As Variant

    Dim chartObjectCollection As New Collection

    'NOTE that this function does not work well with Axis objects.  Excel does not return the correct Parent for them.

    Dim targetObject As Variant
    Dim inputObjectType As String
    inputObjectType = TypeName(inputObject)

    Select Case inputObjectType

        Case "DrawingObjects"
            'this means that multiple charts are selected
            For Each targetObject In inputObject
                If TypeName(targetObject) = "ChartObject" Then
                    'add it to the set
                    chartObjectCollection.Add targetObject
                End If
            Next targetObject

        Case "Worksheet"
            For Each targetObject In inputObject.ChartObjects
                chartObjectCollection.Add targetObject
            Next targetObject

        Case "Chart"
            chartObjectCollection.Add inputObject.Parent

        Case "ChartArea", "PlotArea", "Legend", "ChartTitle"
            'parent is the chart, parent of that is the chart targetObject
            chartObjectCollection.Add inputObject.Parent.Parent

        Case "Series"
            'need to go up three levels
            chartObjectCollection.Add inputObject.Parent.Parent.Parent

        Case "Axis", "Gridlines", "AxisTitle"
            'these are the oddly unsupported objects
            MsgBox "Axis/gridline selection not supported.  This is an Excel bug.  Select another element on the chart(s)."

        Case Else
            MsgBox "Select a part of the chart(s), except an axis."

    End Select

    Set Chart_GetObjectsFromObject = chartObjectCollection
End Function

ChartObjects​

If you are working on a Worksheet, then that Worksheet will have the ChartObjects object. This object is great because it contains all of the Charts in their own collection (separate from any other Shapes or buttons). This ChartObjects collection contains object of type ChartObject. The ChartObject derives from Shape which means it contains all of the properties related to on-sheet position and size.

A typical workflow is included below since it is a pattern that shows up all the time in VBA code related to charts. At a high level the steps are:

  • Use ActiveSheet or a Worksheet reference to access the ChartObjects
  • Iterate through each ChartObject, storing a reference to the underlying Chart
  • You then setup sections to work through the parts of the Chart you want
    • Iterate through the SeriesCollection
    • Iterate through the Axes
    • Touch the other top level properties including ChartTile, Legend, etc.

This workflow is quite powerful because it can quickly be wrapped with a loop to go through all Worksheets and even possible all Workbooks. It's also powerful because you can be quite comfortable learning this pattern and then adding in the parts that you actually want to change. The only downside is that it can be quite tedious to type out all the loops every time, but there's not a good way around that other than to use the clipboard.

Another approach to using ChartObjects is to not iterate through all of them but instead to select a single ChartObject and work with it. There are two ways to do this:

  • Use an integer index for the Chart -- this is quite easy to do if there are only a few charts
  • Name the chart and use that name

When using either of these approaches, it is quite helpful to show the Selection Pane window in Excel. This pane will pop out and tell you the order and the names of all the objects on the sheet (this includes comments, shapes, and Charts). From this pane, you can rearrange the charts into the order you want or rename them.

Although For Each loops are generally preferred when working with Charts, sometimes you simply know that you want to change one chart and an index just lets you do that. If you are in the habit of using loops however, you can easily do that with the helper code included above which stick a single chart into a Collection.

Workbook.Sheets to get Chart references​

The final approach to obtaining a Chart reference is to use the Sheets object. Aside from ActiveChart, this is the only way to deal with Charts that are their own Sheet. Again, you can either use an index or a Name. Here, the Name is easily changed on the Sheet tab so it's much more common to use a Name when doing this. The other approach is to iterate through all the Sheets and pick off the ones that are Charts.

There are two key points when working with Charts as Sheets:

  • You must use the Workbook.Sheets object to access them and not Workbook.Worksheets. The latter object contains only those Worksheets that are not Charts. The former contains both Charts and Worksheets.
  • It's possible that your Sheet is not actually a Chart. You should check the type of the object if you are going to iterate through all Worksheets. Also be aware that some sheets can be hidden which might lead to unexpected results.

TODO: is there a Charts object on Workbook?